<?php
include_once '../mysql/sql-feedback.php';

// ================================================== 按时间查询收支列表
function incspe_list(&$mysql, $params) {
    $incspe = ($params["incspe"] == -1) || ($params["incspe"] == 1)  ? 'and incspe = ' . $params["incspe"] : '';
    if ($params["scope"] == 'day') {
        $date = 'and date = \'' . $params["date"] . '\'';
    }
    else if ($params["scope"] == 'month') {
        $date = 'and year(date) = year(\'' . $params["date"] .'\') and month(date) = month(\'' . $params["date"] .'\')';
    }
    else if ($params["scope"] == 'year') {
        $date = 'and year(date) = year(\'' . $params["date"] .'\')';
    }
    else {
        $date = '';
    };
$statement = <<< EOT
select incspecode, typename, typecode, money, incspe, remark, date
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' $incspe $date
union
select 'total_inc', null, null, ifnull(truncate(sum(money), 2), 0), 1, null, null
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' and t.incspe = 1 $date
union
select 'total_spe', null, null, ifnull(truncate(sum(money), 2), 0), -1, null, null
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' and t.incspe = -1 $date
order by date desc, incspecode desc
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 按最小单位统计
function incspe_statistics(&$mysql, $params) {
    if ($params["smallest"] == 'month') {
        $date = 'and year(i.date) = year(\'' . $params["date"] .'\')';
        $group = 'month(date)';
    }
    else if ($params["smallest"] == 'year') {
        $date = '';
        $group = 'year(date)';
    }
    else {
        $date = 'and year(i.date) = year(\'' . $params["date"] .'\') and month(i.date) = month(\'' . $params["date"] .'\')';
        $group = 'date';
    };
$statement = <<< EOT
select $group as date, incspe, ifnull(truncate(sum(money), 2), 0) as money
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' and t.incspe = {$params["incspe"]} $date
group by $group
union
select 'total', incspe, ifnull(truncate(sum(money), 2), 0)
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' and t.incspe = {$params["incspe"]} $date
order by date desc
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 按类型统计
function incspe_type(&$mysql, $params) {
    if ($params["scope"] == 'day') {
        $date = 'and date = \'' . $params["date"] . '\'';
    }
    else if ($params["scope"] == 'month') {
        $date = 'and year(date) = year(\'' . $params["date"] .'\') and month(date) = month(\'' . $params["date"] .'\')';
    }
    else if ($params["scope"] == 'year') {
        $date = 'and year(date) = year(\'' . $params["date"] .'\')';
    }
    else {
        $date = '';
    };
$statement = <<< EOT
select typename, typecode, incspe, ifnull(truncate(sum(money), 2), 0) as money
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' and t.incspe = {$params["incspe"]} $date
group by typecode
union
select 'total', null, incspe, ifnull(truncate(sum(money), 2), 0)
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' and t.incspe = {$params["incspe"]} $date
order by money desc
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 最近帐单
function incspe_recently(&$mysql, $params) {
$statement = <<< EOT
select incspecode, typename, typecode, money, incspe, remark, date
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}'
order by incspecode desc
limit {$params['count']}
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 获取所有类型
function get_type(&$mysql, $params) {
    $incspe = ($params["incspe"] == -1) || ($params["incspe"] == 1)  ? 'and incspe = ' . $params["incspe"] : '';
$statement = <<< EOT
select typename, typecode, incspe
from types
where user = '{$params["userid"]}' $incspe
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 修改帐单
function update_incspe(&$mysql, $params) {
    $remark = $params["remark"] ? "'" . $params['remark'] . "'" : 'null';
$statement = <<< EOT
update incspes
set typecode = '{$params["typecode"]}', money = '{$incspe["money"]}', remark = $remark, date= '{$incspe["date"]}'
where incspecode='{$params["incspecode"]}'
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 新增帐单
function insert_incspe(&$mysql, $params) {
    $remark = $params["remark"] ? "'" . $params['remark'] . "'" : 'null';
$statement = <<< EOT
insert incspes
values(null, '{$params["typecode"]}', '{$params["money"]}', $remark, '{$params["date"]}', '{$params["userid"]}')
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 新增类别
function insert_type(&$mysql, $params) {
$statement = <<< EOT
insert into types
values(null, '{$params["typename"]}', {$params["incspe"]}, '{$params["userid"]}')
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};
// ================================================== 删除类别
function delete_type(&$mysql, $params) {
$statement = <<< EOT
delete into types
where user = '{$params["userid"]}' and (typecode = '{$params["typecode"]}' or typename = '{$params["typename"]}'
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};
// ================================================== 删除类别的所有记录
function delete_type_incspe(&$mysql, $params) {
$statement = <<< EOT
delete into incspes
where user = '{$params["userid"]}' and type = '{$params["typecode"]}'
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 跟据类别名称查询类别code
function get_typecode(&$mysql, $params) {
$statement = <<< EOT
select *
from types
where user = '{$params["userid"]}' and typename = '{$params["typename"]}' and incspe = '{$params["incspe"]}'
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 获取所有收支总额
function total_money(&$mysql, $params) {
$statement = <<< EOT
select sum(money) as totalMoney
from incspes i inner join types t
on i.type = t.typecode
where i.user = '{$params["userid"]}' and t.incspe = {$params["incspe"]}
group by i.user
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};

// ================================================== 获取用户信息
function user_info(&$mysql, $params) {
$statement = <<< EOT
select *
from users
where id = '{$params["userid"]}'
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};
// ================================================== 设置基础金额
function set_base_money(&$mysql, $params) {
$statement = <<< EOT
update users
set basemoney = '{$params["money"]}'
where id ='{$params["userid"]}'
EOT;
    // echo '<hr />', $statement, '<hr />';
    return sql_feedback($statement, $mysql);
};
?>